package com.mybatispluslambda.poi;

import cn.hutool.core.util.ReflectUtil;
import lombok.Builder;
import lombok.Data;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @className: 成绩整理
 * @description: 成绩整理
 * @author: yanyuhui yanyuhui@chinasofti.com
 * @date: 2020/12/21 14:01
 */
public class RankingSettle {
    public static void main(String[] args) throws Exception {
        InputStream in = new FileInputStream(new File("C:\\Users\\ruby6\\Documents\\WeChat Files\\wxid_mme5040cgwo322\\FileStorage\\File\\2020-12\\" +
                "理科2020-2021学年高三第四次阶段性检测(杨汉中学)(上学期)理科成绩排名.xls"));
        Map<String, List<RankDTO>> map = new HashMap<>(200);
        try (HSSFWorkbook wb = new HSSFWorkbook(in)) {
            HSSFSheet sheet = wb.getSheetAt(1);
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < physicalNumberOfRows; i++) {
                // 正文数据第三行起
                if (i >= 3) {
                    HSSFRow row = sheet.getRow(i);
                    HSSFCell number = row.getCell(0);
                    HSSFCell name = row.getCell(1);
                    HSSFCell clazz = row.getCell(2);
                    HSSFCell ranking = row.getCell(3);
                    HSSFCell shengwu = row.getCell(9);
                    String clazzString = getString(clazz);
                    RankDTO dto = RankDTO.builder().number(getNumberString(number))
                            .name(getString(name)).clazz(getNumberString(clazz)).ranking(getString(ranking))
                            .shengwu(getString(shengwu)).build();
                    if (map.containsKey(clazzString)) {
                        map.get(clazzString).add(dto);
                    } else {
                        List<RankDTO> rankDTOS = new LinkedList<>();
                        rankDTOS.add(dto);
                        map.put(clazzString, rankDTOS);
                    }
                }
            }

            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
            FileOutputStream out = new FileOutputStream("C:\\Users\\ruby6\\Desktop\\仙女请验收哦x.xls");
            for (Map.Entry<String, List<RankDTO>> entry : map.entrySet()) {
                HSSFSheet sheets = hssfWorkbook.createSheet(entry.getKey() + "班成绩");
                // 创建单元格样式
                HSSFCellStyle style = hssfWorkbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER); //文字水平居中
                style.setVerticalAlignment(VerticalAlignment.CENTER);//文字垂直居中
                style.setBorderBottom(BorderStyle.THIN); //底边框加黑
                style.setBorderLeft(BorderStyle.THIN);  //左边框加黑
                style.setBorderRight(BorderStyle.THIN); // 有边框加黑
                style.setBorderTop(BorderStyle.THIN); //上边框加黑
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置前景填充样式
                style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());//前景填充色

                List<RankDTO> result = entry.getValue();
                for (int i = 0; i < result.size(); i++) {
                    Row row = sheets.createRow(i);
                    row.setRowStyle(style);
                }
                sheets.createRow(result.size());
//                sheets.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

                HSSFRow headerRow = sheets.getRow(0);
                headerRow.createCell(0).setCellValue("考号");
                headerRow.createCell(1).setCellValue("班级");
                headerRow.createCell(2).setCellValue("姓名");
                headerRow.createCell(3).setCellValue("总分/班位次/级位次");
                headerRow.createCell(4).setCellValue("生物/班位次/级位次");

                for (int i = 1; i < result.size(); i++) {
                    HSSFRow row = sheets.getRow(i);
                    row.createCell(0).setCellValue(result.get(i).getNumber());
                    row.createCell(1).setCellValue(result.get(i).getClazz());
                    row.createCell(2).setCellValue(result.get(i).getName());
                    row.createCell(3).setCellValue(result.get(i).getRanking());
                    row.createCell(4).setCellValue(result.get(i).getShengwu());
                }
            }
            hssfWorkbook.write(out);
        }
    }

    public static String getNumberString(HSSFCell hssfCell) {
        Object invoke = ReflectUtil.invoke(hssfCell, "convertCellValueToString");
        return invoke.toString();
    }

    public static String getString(Object obj) {
        return obj.toString();
    }

    @Data
    @Builder
    public static class RankDTO {
        private String number;
        private String name;
        private String clazz;
        private String ranking;
        private String shengwu;
    }

}
